package dapps.dao.monitor;

import java.util.List;
import java.util.ListIterator;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.hibernate.HibernateDao;

import dapps.entity.monitor.Cominput;
import dapps.entity.monitor.Fieldvalue;
import dapps.entity.monitor.Indicator;
import dapps.entity.monitor.Servicedata;
import dapps.entity.monitor.Servicemain;
import dapps.entity.monitor.Servicepredi;
import dapps.entity.monitor.Smvalue;
import dapps.entity.user.User;

//Spring DAO Bean的标识
@Repository
public class MonitorDao extends HibernateDao<Servicemain, Long> {
	
	private final static  String QUERY_BY_CompanyMonitor_HQL 
	= "SELECT user as user, sm as sm FROM User user left outer join user.servicemains sm with sm.reportyearmonth = ? where user.type.tid>0  ";
	
	public List<Servicemain> getUsersOrderByRole(String reportDate,Long districtid) {
		StringBuilder sb = new StringBuilder(QUERY_BY_CompanyMonitor_HQL);
		if(districtid!=null&&districtid>1L){
			sb.append(" and user.district.id=?");
			return find(sb.toString(), reportDate,districtid);				
		}else{
			sb.append(" and user.iscity=1");
			return find(sb.toString(), reportDate);
		}	
		
	}
	
	public Servicedata getData(Long mainid) {
		return (Servicedata) getSession().load(Servicedata.class, mainid);		
	}
	
	public Servicepredi getDatapredi(Long mainid) {
		return (Servicepredi) getSession().load(Servicepredi.class, mainid);
	}
	public void saveService(Servicemain sm,Servicedata sd,Servicepredi sp){
		getSession().saveOrUpdate(sm);
		if(sd.getMainid()==null) sd.setMainid(sm.getId());
		if(sp.getMainid()==null) sp.setMainid(sm.getId());
		getSession().saveOrUpdate(sd);
		getSession().saveOrUpdate(sp);
	}
	

	
	private static  String QUERY_BY_CountinputCompany_HQL 
	="select count(*) from Servicemain s, Servicedata sd,Servicepredi sp where s.reportyearmonth=? and sd.mainid=s.id and sp.mainid=s.id ";
	
	public int getInputCount(String reportDate,Long districtid) {
		Query q;
		StringBuilder sb = new StringBuilder(QUERY_BY_CountinputCompany_HQL);
		if(districtid!=null&&districtid>1L){
			sb.append(" and s.dutycomp.district.id=?");
			q = createQuery(sb.toString(),reportDate,districtid);
		}else{
			sb.append(" and s.dutycomp.iscity=1");
			q = createQuery(sb.toString(),reportDate);
		}
		
		return  ((Long) q.uniqueResult()).intValue();		
	}
	
	private static final String QUERY_SmId_HQL 
	= "SELECT sm.id FROM Servicemain sm where  sm.reportyearmonth = ? and sm.dutycomp.id = ?  ";
	
	public Long getSmId(String reportDate,Long userid) {
		return findLong(QUERY_SmId_HQL,reportDate,userid);
	
	}
	
	private static  String QUERY_BY_CountCompany_HQL 
	= "SELECT count(u)  FROM User u  where u.type.tid>0";
	
	private static  String QUERY_BY_CompanyId_HQL 
	= "SELECT user as user, sm as sm FROM User user left outer join user.servicemains sm with sm.reportyearmonth = ? where user.code = ?  ";
	
	@SuppressWarnings("unchecked")
	public Page<Cominput> getSmPageByDate(Page<Cominput> page ,String reportDate,Integer usercode,Long districtid) {
		Query q;
		StringBuilder sb;
		if(usercode==null||usercode.intValue()<1){
			if (page.isAutoCount()) {	
				sb = new StringBuilder(QUERY_BY_CountCompany_HQL);
				if(districtid!=null&&districtid>1L){
					sb.append("  and u.district.id =? ");
					q = createQuery(sb.toString(),districtid);
				}else{
					sb.append(" and u.iscity=1");
					q = createQuery(sb.toString());
				}
				int totalCount = ((Long) q.uniqueResult()).intValue();
				page.setTotalCount(totalCount);			
			}
			String orderstr="";
			if (page.isOrderBySetted()) {
				String[] orderByArray = StringUtils.split(page.getOrderBy(), ',');
				String[] orderArray = StringUtils.split(page.getOrder(), ',');
				orderstr += "order by " ;

				Assert.isTrue(orderByArray.length == orderArray.length,
						"分页多重排序参数中,排序字段与排序方向的个数不相等");

				for (int i = 0; i < orderByArray.length; i++) {
					if (Page.ASC.equals(orderArray[i])) {
						orderstr += orderByArray[i] +" asc ";
					} else {
						orderstr += orderByArray[i] +" desc ";
					}
				}
			}
			q=null;		
			if(StringUtils.isNotBlank(reportDate)){
				sb = new StringBuilder(QUERY_BY_CompanyMonitor_HQL);
				if(districtid!=null&&districtid>1L){
					sb.append(" and user.district.id=? ").append(orderstr);
					q = createQuery(sb.toString(),reportDate,districtid);
				}else{
					sb.append(" and user.iscity=1").append(orderstr);
					q = createQuery(sb.toString(),reportDate);
				}
			}else{
				return null;
			}
		}else{
			page.setTotalCount(1);	
			if(StringUtils.isNotBlank(reportDate)){
				sb = new StringBuilder(QUERY_BY_CompanyId_HQL);
				if(districtid!=null&&districtid>1L){
					sb.append(" and user.district.id=? ");
					q = createQuery(sb.toString(),reportDate,usercode,districtid);
				}else{
					sb.append(" and user.iscity=1");
					q = createQuery(sb.toString(),reportDate,usercode);
				}
			}else{
				return null;
			}	
		}
				
		q.setFirstResult(page.getFirst());
		q.setMaxResults(page.getPageSize());		
		q.setResultTransformer( Transformers.aliasToBean(Cominput.class) );

		List result = q.list();
		page.setResult(result);
		return page;
	}

	private final static  String QUERY_BY_CompanyMonitor_HQLsms 
	= " FROM User user  where user.id not in (select servicemain.dutycomp.id from Servicemain servicemain where reportyearmonth=?) and user.type.tid>0  ";
	private static  String QUERY_BY_CompanyId_HQLsms
	= " FROM User user  where user.id not in (select servicemain.dutycomp.id from Servicemain servicemain where reportyearmonth=?) and  user.code = ?  ";
	
	@SuppressWarnings("unchecked")
	public List<User> getSmsPageByDate(String reportDate,Integer usercode,Long districtid) {
		Query q;
		StringBuilder sb;
		if(usercode==null||usercode.intValue()<1){
			q=null;		
			if(StringUtils.isNotBlank(reportDate)){
				sb = new StringBuilder(QUERY_BY_CompanyMonitor_HQLsms);
				if(districtid!=null&&districtid>1L){
					sb.append(" and user.district.id=? and user.iscity=0 ");
					q = createQuery(sb.toString(),reportDate,districtid);
				}else{
					sb.append(" and user.iscity=1");
					q = createQuery(sb.toString(),reportDate);
				}
			}else{
				return null;
			}
		}else{
			if(StringUtils.isNotBlank(reportDate)){
				sb = new StringBuilder(QUERY_BY_CompanyId_HQLsms);
				if(districtid!=null&&districtid>1L){
					sb.append(" and user.district.id=?  and user.iscity=0 ");
					q = createQuery(sb.toString(),reportDate,usercode,districtid);
				}else{
					sb.append(" and user.iscity=1");
					q = createQuery(sb.toString(),reportDate,usercode);
				}
			}else{
				return null;
			}	
		}

		return  q.list();
	}
	
	@SuppressWarnings("unchecked")
	public Cominput getSmPageByDate(String reportDate,Integer userid,Long districtid) {
		Query q;
		if(StringUtils.isNotBlank(reportDate)){
			StringBuilder sb = new StringBuilder(QUERY_BY_CompanyId_HQL);
			if(districtid!=null&&districtid>1L){
				sb.append(" and user.district.id=? ");
				q = createQuery(sb.toString(),reportDate,userid,districtid);
			}else{
				sb.append(" and user.iscity=1");
				q = createQuery(sb.toString(),reportDate,userid);
			}
		}else{
			return null;
		}	
		q.setResultTransformer( Transformers.aliasToBean(Cominput.class) );

		ListIterator iterator = q.list().listIterator();
		Cominput c = null;
		if (iterator.hasNext()) {
			c = (Cominput)iterator.next();
		}
		return c;
	}
	
	@SuppressWarnings("unchecked")
	public Map<String,Integer> getStaticsPredi(String reportDate,Long typeid,Long districtid) {
		Query q;
		String sumstr=" ";
		Servicepredi sp = new Servicepredi();
		for(String[] sarray:sp.getFields()){		
			int k = Integer.parseInt(sarray[0]);
			for(int i=1;i<sarray.length;i++){				
				if(k>0){
					for(int j=1;j<=k;j++){
						sumstr+=" sum( case when "+sarray[i]+"="+j+" then 1 else 0 end) as "+sarray[i]+"_"+j+",";
					}
					sumstr+=" sum( case when "+sarray[i]+">"+0+" then 1 else 0 end) as "+sarray[i]+"_"+0+",";
				}
				else if(k==0){//sum( case when substring(profitfactor,1,1)=1 then 1 else 0 end) p11
					for(int j=1;j<=7;j++){
						sumstr += "sum( case when substring("+sarray[i]+",1,1)="+j+" then 1 else 0 end) as "+sarray[i]+"_"+j+"_"+1+",";
						sumstr += "sum( case when substring("+sarray[i]+",2,1)="+j+" then 1 else 0 end) as "+sarray[i]+"_"+j+"_"+2+",";
						sumstr += "sum( case when substring("+sarray[i]+",3,1)="+j+" then 1 else 0 end) as "+sarray[i]+"_"+j+"_"+3+",";
					}
				}				
			}
		}
		
		String sql ="";
		if (StringUtils.isNotBlank(reportDate)) {
			if (typeid != null && typeid > 0l) {
				sql = "select "
						+ sumstr
						+ "count(sp.mainid) as mainidcount from Servicemain sm , Servicepredi sp ,User u " 
						+ "where u.id=sm.dutycomp and  sm.reportyearmonth= ? and sm.id = sp.mainid and u.type.id=? ";
				if(districtid!=null&&districtid>1L){//区县
					sql=sql+"and u.district.id=?";
					q = createQuery(sql, reportDate,typeid,districtid);					
				}else{//市级
					sql=sql+"and u.iscity=1";
					q = createQuery(sql, reportDate,typeid);
				}
			} else {
				sql = "select "
						+ sumstr
						+ "count(sp.mainid) as mainidcount from Servicemain sm , Servicepredi sp " +
								"where sm.reportyearmonth= ? and sm.id = sp.mainid ";
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=?";
					q = createQuery(sql, reportDate,districtid);					
				}else{
					sql=sql+" and sm.dutycomp.iscity=1";
					q = createQuery(sql, reportDate);
				}			
			}
		} else {
			return null;
		}
		q.setResultTransformer( Transformers.ALIAS_TO_ENTITY_MAP );

		ListIterator iterator = q.list().listIterator();

		Map<String, Integer> values = null;
		if (iterator.hasNext()) {
			values = (Map<String, Integer>) iterator.next();
		}
		return values;
	}
	

	@SuppressWarnings("unchecked")
	public Map<String,Double> getStaticsData(String reportDate,Long typeid,Long districtid) {
		Query q;
		String sumstr=" ";
		Servicedata sd = new Servicedata();
		for(String[] sarray:sd.getFields()){		
			int k = Integer.parseInt(sarray[0]);
			for(int i=1;i<sarray.length;i++){
				if(k==1) sumstr+=" sum( case when sd."+sarray[i]+"=2 then 1 else 0 end) as sum"+sarray[i]+",";
				else if(k==2)  sumstr+=" sum(sd."+sarray[i]+") as sum"+sarray[i]+",";				
			}
		}
		
		String sql = "";
		if (StringUtils.isNotBlank(reportDate)) {
			if (typeid != null && typeid > 0l) {
				sql = "select "
						+ sumstr
						+ "count(sd.mainid) as mainidcount from Servicemain sm , Servicedata sd ,User u " +
								"where u.id=sm.dutycomp and  sm.reportyearmonth= ? and sm.id = sd.mainid and u.type.id=? ";
				if(districtid!=null&&districtid>1L){
					sql=sql+"and u.district.id=?";
					q = createQuery(sql, reportDate,typeid,districtid);					
				}else{
					sql=sql+"and u.iscity=1";
					q = createQuery(sql, reportDate,typeid);
				}
			} else {
				sql = "select "
						+ sumstr
						+ "count(sd.mainid) as mainidcount from Servicemain sm , Servicedata sd where sm.reportyearmonth= ? and sm.id = sd.mainid  ";
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=?";
					q = createQuery(sql, reportDate,districtid);					
				}else{
					sql=sql+" and sm.dutycomp.iscity=1";
					q = createQuery(sql, reportDate);
				}	
			}
		} else {
			return null;
		}

		q.setResultTransformer( Transformers.ALIAS_TO_ENTITY_MAP );

		ListIterator iterator = q.list().listIterator();

		Map<String, Double> values = null;
		if (iterator.hasNext()) {
			values = (Map<String, Double>) iterator.next();
		}
		return values;
	}
	
	@SuppressWarnings("unchecked")
	public Map<String,Double> getStaticsLastmonth(String reportDate,Long typeid,Long districtid) {
		Query q;
		String sumstr=" ";
		Servicedata sd = new Servicedata();
		for(String[] sarray:sd.getlastmonthFields()){		
			int k = Integer.parseInt(sarray[0]);
			for(int i=1;i<sarray.length;i++){
				if(k==3)  sumstr+=" sum(sd."+sarray[i]+") as sum"+sarray[i]+",";				
			}
		}
		
		String sql = "";
		if (StringUtils.isNotBlank(reportDate)) {
			
			int thismonth = Integer.parseInt(reportDate);
			int lastmonth = thismonth -1;
			if(thismonth%100==1) lastmonth = thismonth-89;//-101+12
			
			if (typeid != null && typeid > 0l) {
				sql = "select "
						+ sumstr
						+ "count(sd.mainid) as mainidcount from Servicemain sm , Servicedata sd ,User u " +
								"where u.id=sm.dutycomp and  sm.reportyearmonth= ? and sm.id = sd.mainid and u.type.id=? ";				
				if(districtid!=null&&districtid>1L){
					sql=sql+" and u.district.id=?";
					q = createQuery(sql, String.valueOf(lastmonth),typeid,districtid);					
				}else{
					sql=sql+" and u.iscity=1";
					q = createQuery(sql, String.valueOf(lastmonth),typeid);
				}

			} else {
				sql = "select "
						+ sumstr
						+ "count(sd.mainid) as mainidcount from Servicemain sm , Servicedata sd " +
								"where sm.reportyearmonth= ? and sm.id = sd.mainid  ";				
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=?";
					q = createQuery(sql, String.valueOf(lastmonth),districtid);					
				}else{
					sql=sql+" and sm.dutycomp.iscity=1";
					q = createQuery(sql, String.valueOf(lastmonth));
				}	
			}
		} else {
			return null;
		}

		
		q.setResultTransformer( Transformers.ALIAS_TO_ENTITY_MAP );

		ListIterator iterator = q.list().listIterator();

		Map<String, Double> values = null;
		if (iterator.hasNext()) {
			values = (Map<String, Double>) iterator.next();
		}
		return values;
	}
	
	@SuppressWarnings("unchecked")
	public List<Indicator> getStaticsIndicator(String reportDate,Long typeid,Long districtid) {
		Query q;
		
		String sql = "SELECT sum(i.value1) as value1,sum(i.value2) as value2,sum(i.value3) as value3,i.pk.element as element " +
					 "FROM Indicator i  " +
					 "where  i.pk.servicemain.reportyearmonth = ? and i.pk.element.type.id =? ";
	
		if(StringUtils.isNotBlank(reportDate)&&typeid!=null&&typeid>0){			
			if(districtid!=null&&districtid>1L){
				sql=sql+" and i.pk.servicemain.dutycomp.district.id=? group by i.pk.element ";
				q = createQuery(sql, reportDate,typeid,districtid);					
			}else{
				sql=sql+" and i.pk.servicemain.dutycomp.iscity=1 group by i.pk.element ";
				q = createQuery(sql, reportDate,typeid);
			}
		}else{
			return null;
		}	
		q.setResultTransformer( Transformers.aliasToBean(Indicator.class));

		return q.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Indicator> getStaticsIndicators(String reportDate,Long districtid) {
		Query q;
		
		String sql = "SELECT sum(i.value1) as value1,sum(i.value2) as value2,sum(i.value3) as value3,i.pk.element as element " +
					 "FROM Indicator i  " +
					 "where  i.pk.servicemain.reportyearmonth = ?";
	
		if(StringUtils.isNotBlank(reportDate)){
			if(districtid!=null&&districtid>1L){
				sql=sql+" and i.pk.servicemain.dutycomp.district.id=? group by i.pk.element ";
				q = createQuery(sql, reportDate,districtid);					
			}else{
				sql=sql+" and i.pk.servicemain.dutycomp.iscity=1 group by i.pk.element ";
				q = createQuery(sql, reportDate);
			}
		}else{
			return null;
		}	
		q.setResultTransformer( Transformers.aliasToBean(Indicator.class));

		return q.list();
	}	
	@SuppressWarnings("unchecked")
	public List<Fieldvalue> getStaticsOthers(String reportDate,String[] fields,String otherno,String otherrank,Long typeid,Long districtid) {
		Query q;
		
		String sql = "";
	
		if (StringUtils.isNotBlank(reportDate)) {
			
			if (typeid != null && typeid > 0l) {
				sql = "SELECT sm.dutycomp.name as name ,s."
							+fields[1]
					        +" as value  FROM Servicepredi s,Servicemain sm  where sm.reportyearmonth= ? and sm.dutycomp.type.id=?  and sm.id = s.mainid  and INSTR(s."+fields[0]+" ,"+otherno+")="
					        +otherrank;
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=?  ";
					q = createQuery(sql, reportDate,typeid,districtid);					
				}else{
					sql=sql+" and sm.dutycomp.iscity=1 ";
					q = createQuery(sql, reportDate,typeid);
				}
			} else {
				sql = "SELECT sm.dutycomp.name as name ,s."
							+fields[1]
					        +" as value  FROM Servicepredi s,Servicemain sm  where sm.reportyearmonth= ? and sm.id = s.mainid   and INSTR(s."+fields[0]+" ,"+otherno+")="
					        +otherrank;
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=? ";
					q = createQuery(sql, reportDate,districtid);					
				}else{
					sql=sql+" and sm.dutycomp.iscity=1  ";
					q = createQuery(sql, reportDate);
				}
			}
		} else {
			return null;
		}

		q.setResultTransformer( Transformers.aliasToBean(Fieldvalue.class));
		return q.list();
	}

	@SuppressWarnings("unchecked")
	public List<Fieldvalue> getProblem(String reportDate, String field,Long districtid) {
		Query q;
		String sql = "";
		sql = "SELECT sm.dutycomp.name as name ,s."
				+ field
				+ " as value  FROM Servicedata s,Servicemain sm  where sm.reportyearmonth= ? and sm.id = s.mainid and length(s."+field+")>0";
		if (districtid != null && districtid > 1L) {
			sql = sql + " and sm.dutycomp.district.id=? ";
			q = createQuery(sql, reportDate, districtid);
		} else {
			sql = sql + " and sm.dutycomp.iscity=1  ";
			q = createQuery(sql, reportDate);
		}

		q.setResultTransformer(Transformers.aliasToBean(Fieldvalue.class));
		return q.list();
	}
	private static  String QUERY_BY_CountSmType_HQL 
	= "SELECT count(*) from Servicemain sm  where sm.reportyearmonth = ? and sm.dutycomp.type.id = ?  ";
	
	private static  String QUERY_BY_CountSm_HQL 
	= "SELECT count(*) from Servicemain sm  where  sm.reportyearmonth = ?  ";
	
	@SuppressWarnings("unchecked")
	public Page<Smvalue> getRank(Page<Smvalue> page, String reportDate,Long typeid,Long districtid) {
		Query q = null;
		String sql="";
		
		if (StringUtils.isNotBlank(reportDate)) {

			String orderstr = "";
			if (page.isOrderBySetted()) {
				String[] orderByArray = StringUtils.split(page.getOrderBy(), ',');
				String[] orderArray = StringUtils.split(page.getOrder(), ',');
				orderstr += "order by ";

				Assert.isTrue(orderByArray.length == orderArray.length,
						"分页多重排序参数中,排序字段与排序方向的个数不相等");

				for (int i = 0; i < orderByArray.length; i++) {
					if (Page.ASC.equals(orderArray[i])) {
						orderstr += orderByArray[i] + " asc ";
					} else {
						orderstr += orderByArray[i] + " desc ";
					}
				}
			}
			int totalCount = 0;
			if (typeid != null && typeid > 0l) {
				if (page.isAutoCount()) {
					StringBuilder sb = new StringBuilder(QUERY_BY_CountSmType_HQL);
					if(districtid!=null&&districtid>1L){
						sb.append(" and sm.dutycomp.district.id=? ");
						q = createQuery(sb.toString(), reportDate,typeid,districtid);					
					}else{
						sb.append(" and sm.dutycomp.iscity=1  ");
						q = createQuery(sb.toString(), reportDate,typeid);
					}
					totalCount = ((Long) q.uniqueResult()).intValue();
					page.setTotalCount(totalCount);
				}				
				sql = "SELECT sm.dutycomp as user,s.mincomelm as value1,s.mincomelmsum as value2,s.mincomelysum as value3,s.profitaftaxlm as value4,s.profitaftaxlmsum as value5,s.profitaftaxlysum as value6 FROM Servicedata s,Servicemain sm  " +
						"where sm.reportyearmonth= ? and s.mainid=sm.id and sm.dutycomp.type.id=?";				
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=? ";
					q = createQuery(sql+orderstr, reportDate,typeid,districtid);
				}else{
					sql=sql+" and sm.dutycomp.iscity=1  ";
					q = createQuery(sql+orderstr, reportDate,typeid);
				}

			} else {
				if (page.isAutoCount()) {
					StringBuilder sb = new StringBuilder(QUERY_BY_CountSm_HQL);
					if(districtid!=null&&districtid>1L){
						sb.append(" and sm.dutycomp.district.id=? ");
						q = createQuery(sb.toString(), reportDate,districtid);
					}else{
						sb.append(" and sm.dutycomp.iscity=1  ");
						q = createQuery(sb.toString(), reportDate);
					}		
					totalCount = ((Long) q.uniqueResult()).intValue();
					page.setTotalCount(totalCount);
				}
				sql = "SELECT sm.dutycomp as user,s.mincomelm as value1,s.mincomelmsum as value2,s.mincomelysum as value3,s.profitaftaxlm as value4,s.profitaftaxlmsum as value5,s.profitaftaxlysum as value6 FROM Servicedata s,Servicemain sm  " +
						"where sm.reportyearmonth= ? and s.mainid=sm.id  and sm.id = s.mainid  ";
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=? ";
					q = createQuery(sql+orderstr, reportDate,districtid);
				}else{
					sql=sql+" and sm.dutycomp.iscity=1  ";
					q = createQuery(sql+orderstr, reportDate);
				}
			}
		} else {
			return null;
		}

		
		q.setFirstResult(page.getFirst());
		q.setMaxResults(page.getPageSize());
		q.setResultTransformer(Transformers.aliasToBean(Smvalue.class));

		List<Smvalue> result = q.list();
		page.setResult(result);
		return page;
	}
	@SuppressWarnings("unchecked")
	public List<Smvalue> getRankList(Page<Smvalue> page, String reportDate,Long typeid,Long districtid) {
		Query q = null;
		String sql="";		
		if (StringUtils.isNotBlank(reportDate)) {
			String orderstr = "";
			if (page.isOrderBySetted()) {
				String[] orderByArray = StringUtils.split(page.getOrderBy(), ',');
				String[] orderArray = StringUtils.split(page.getOrder(), ',');
				orderstr += "order by ";
				Assert.isTrue(orderByArray.length == orderArray.length,
						"分页多重排序参数中,排序字段与排序方向的个数不相等");

				for (int i = 0; i < orderByArray.length; i++) {
					if (Page.ASC.equals(orderArray[i])) {
						orderstr += orderByArray[i] + " asc ";
					} else {
						orderstr += orderByArray[i] + " desc ";
					}
				}
			}
			if (typeid != null && typeid > 0l) {			
				sql = "SELECT sm.dutycomp as user," +
						" s.mincomelm as value1,s.mincomelmsum as value2,s.mincomelysum as value3," +
						" s.profitaftaxlm as value4,s.profitaftaxlmsum as value5,s.profitaftaxlysum as value6," +
						" s.costlm as value7,s.costlmsum as value8,s.costlysum as value9 "+
						" FROM Servicedata s,Servicemain sm  " +
						" where sm.reportyearmonth= ? and s.mainid=sm.id and sm.dutycomp.type.id=?";				
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=? ";
					q = createQuery(sql+orderstr, reportDate,typeid,districtid);
				}else{
					sql=sql+" and sm.dutycomp.iscity=1  ";
					q = createQuery(sql+orderstr, reportDate,typeid);
				}
			} else {				
				sql = "SELECT sm.dutycomp as user," +
						"s.mincomelm as value1,s.mincomelmsum as value2,s.mincomelysum as value3," +
						"s.profitaftaxlm as value4,s.profitaftaxlmsum as value5,s.profitaftaxlysum as value6, " +
						" s.costlm as value7,s.costlmsum as value8,s.costlysum as value9 "+
						"FROM Servicedata s,Servicemain sm  " +
						"where sm.reportyearmonth= ? and s.mainid=sm.id  and sm.id = s.mainid  ";
				if(districtid!=null&&districtid>1L){
					sql=sql+" and sm.dutycomp.district.id=? ";
					q = createQuery(sql+orderstr, reportDate,districtid);
				}else{
					sql=sql+" and sm.dutycomp.iscity=1  ";
					q = createQuery(sql+orderstr, reportDate);
				}
			}
		} else {
			return null;
		}
		q.setResultTransformer(Transformers.aliasToBean(Smvalue.class));

		return q.list();
	}

}
